*--------------------------------------------------
* 11/20, version 1
* Nathan Goldschlag
* 
*--------------------------------------------------

*--------------------------------------------------
* PROGRAM SETUP
*--------------------------------------------------
set more off
set type double
clear all
set linesize 80
macro drop _all
capture log close
di c(current_date) " " c(current_time)
local dt="`c(current_date)' `c(current_time)'"
local dt=subinstr("`dt'",":","",.)
local dt=subinstr("`dt'"," ","",.)
log using ./r2_make_tables_figures.log, replace
di c(current_date) " " c(current_time)
* --------------------------------------------------
include 0_config.do

* --------------------------------------------------
* CLEAN OUT OLD TABLES AND FIGURES
* --------------------------------------------------
* this ensures that we always have up-to-date results
shell rm $fig*
shell rm $tbl*

* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* TABLES
* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

* --------------------------------------------------
* BALANCE
import excel using $out/extras.xlsx, sheet(o3.17) clear first
gen order = . 
replace order = 1 if _n==3
replace order = 2 if _n==2
replace order = 3 if _n==4
replace order = 4 if _n==6
replace order = 5 if _n==5
replace order = 6 if _n==1
sort order
rename mean1 Treated
rename mean0 Control 
rename diff Difference
rename var label
order label Treated Control Difference se 
keep label Treated Control Difference se 
outsheet using $tbl/balance2.tex, delimiter("&") replace noquote non

* --------------------------------------------------
* FIRM DEATH BY EJ V FDR
clear
tempfile tbl
tempfile fnltbl
foreach fdr of numlist 1(1)2 {
    foreach i of numlist 1(1)5 {
        if `fdr' == 1 {
            local s = 17
        }
        else {
            local s = 22
        }
        local s = `s' + `i'
        import excel using $out/extras.xlsx, sheet(o3.`s') clear first
        replace parm = ""
        tempfile p1
        gen e = estimate
    	tostring estimate, replace force
    	replace estimate = "" if estimate == "."
    	replace estimate = estimate + "***" if (abs(e/stderr)) > 3.291 & regexm(estimate,"\*") != 1 
    	replace estimate = estimate + "**" if (abs(e/stderr)) > 1.96 & regexm(estimate,"\*") != 1 
    	replace estimate = estimate + "*" if (abs(e/stderr)) > 1.645 & regexm(estimate,"\*") != 1 
		drop e
        foreach v of varlist estimate stderr rsq n {
            preserve
                keep parm `v'
                rename `v' dthby`i'
                tostring dthby`i', replace force
                if "`v'"=="estimate" {
                    save `p1'
                }
                else {
                    append using `p1'
                    save `p1', replace
                }
            restore
        }
        use `p1', clear
        list
        gen i = _n
        gsort -i
        replace parm = "Treated" if _n==1
        replace dthby`i' = "(" + dthby`i' + ")" if _n==2
        replace parm = "\$R^2\$" if _n==3
        replace parm = "N" if _n==4
        list
        if `i' > 1 {
            merge 1:1 i using `tbl', nogen
            save `tbl', replace
        }
        else {
            save `tbl', replace
        }
    }
    use `tbl', clear
    gsort -i
    keep parm dth*
    order parm dthby1 dthby2 dthby3 dthby4 dthby5
    if `fdr' == 1 {
        save `fnltbl'
    }
    else {
        set obs 5
        append using `fnltbl'
        save `fnltbl', replace
    }
}
use `fnltbl', clear
set obs 10
gen sort = _n
replace sort = .5 if _n==10
sort sort
drop if missing(dthby1)
drop sort
set obs 10
gen n = _n
replace n = 2.1 if n==9
replace n = 6.1 if n==10
sort n
drop n
outsheet using $tbl/deathlpm_ejfdr.tex, delimiter("&") replace noquote non

* --------------------------------------------------
* DEATH SHOCK MAIN fndr INTERACTION
import excel using $out/extras.xlsx, sheet(o3.13) clear first
makeRegTable
rename estimate lnemp
gen n=_n
keep parm lnemp n
tempfile p1a
save `p1a'

import excel using $out/extras.xlsx, sheet(o3.14) clear first
makeRegTable
rename estimate lnrev
gen n=_n
keep parm lnrev n
tempfile p1b
save `p1b'

import excel using $out/dhs_emp.xlsx, sheet(o1.1) clear first
makeRegTable
rename estimate dhsemp 
gen n= _n
keep parm dhsemp n
tempfile p2
save `p2'

import excel using $out/dhs_rev.xlsx, sheet(o2.1) clear first
makeRegTable
rename estimate dhsrev 
gen n= _n
keep parm dhsrev n
merge 1:1 n using `p1a', nogen
merge 1:1 n using `p1b', nogen
merge 1:1 n using `p2', nogen
keep parm dhsemp dhsrev lnemp lnrev 
order parm dhsemp dhsrev lnemp lnrev 
outsheet using $tbl/mainfndr.tex, delimiter("&") replace noquote non

* --------------------------------------------------
* COMBINED HET EFF
getDHSMainReg 3
replace parm = subinstr(parm,"Yg Firm","[Het Eff]",.)
rename (dhsemp dhsrev) (emp1 rev1)
gen n =_n
tostring n, replace
gen parm_n = parm+n
drop n
tempfile p1
save `p1'

getDHSMainReg 4
replace parm = subinstr(parm,"B2B","[Het Eff]",.)
rename (dhsemp dhsrev) (emp2 rev2)
gen n =_n
tostring n, replace
gen parm_n = parm+n
drop n
tempfile p2
save `p2'

getDHSMainReg 5
replace parm = subinstr(parm,"Small","[Het Eff]",.)
rename (dhsemp dhsrev) (emp3 rev3)
gen n =_n
tostring n, replace
gen parm_n = parm+n
drop n
tempfile p3
save `p3'

getDHSMainReg 6
replace parm = subinstr(parm,"Sole Prop","[Het Eff]",.)
rename (dhsemp dhsrev) (emp4 rev4)
gen n =_n
tostring n, replace
gen parm_n = parm+n
merge 1:1 parm_n using `p1', nogen
merge 1:1 parm_n using `p2', nogen
merge 1:1 parm_n using `p3', nogen
destring n, replace 
sort n 
replace parm = subinstr(parm,"Founder","F",.)
replace parm = subinstr(parm,"Treated","T",.)
replace parm = subinstr(parm,"Post","P",.)
order parm emp1 rev1 emp2 rev2 emp3 rev3 emp4 rev4
keep parm emp1 rev1 emp2 rev2 emp3 rev3 emp4 rev4
outsheet using $tbl/did_hetef_fdr.tex, delimiter("&") replace noquote non 

* --------------------------------------------------
* COMBINED HET EFF SKILLS
getDHSMainReg 7
replace parm = subinstr(parm,"College","[Het Eff]",.)
rename (dhsemp dhsrev) (emp1 rev1)
gen n =_n
tostring n, replace
gen parm_n = parm+n
drop n
tempfile p1
save `p1'

getDHSMainReg 8
replace parm = subinstr(parm,"HT","[Het Eff]",.)
rename (dhsemp dhsrev) (emp2 rev2)
gen n =_n
tostring n, replace
gen parm_n = parm+n
drop n
tempfile p2
save `p2'

getDHSMainReg 9
replace parm = subinstr(parm,"Abst Task","[Het Eff]",.)
rename (dhsemp dhsrev) (emp3 rev3)
gen n =_n
tostring n, replace
gen parm_n = parm+n
drop n
tempfile p3
save `p3'

getDHSMainReg 10
replace parm = subinstr(parm,"HCT","[Het Eff]",.)
rename (dhsemp dhsrev) (emp4 rev4)
gen n =_n
replace n= 2.1 if n==7
replace n= 2.2 if n==8
sort n
drop n
gen n = _n
tostring n, replace
gen parm_n = parm+n
merge 1:1 parm_n using `p1', nogen
merge 1:1 parm_n using `p2', nogen
merge 1:1 parm_n using `p3', nogen
destring n, replace 
sort n 
replace parm = subinstr(parm,"Founder","F",.)
replace parm = subinstr(parm,"Treated","T",.)
replace parm = subinstr(parm,"Post","P",.)
order parm emp1 rev1 emp2 rev2 emp3 rev3 emp4 rev4
keep parm emp1 rev1 emp2 rev2 emp3 rev3 emp4 rev4
outsheet using $tbl/did_hetef_skill_fdr.tex, delimiter("&") replace noquote non 

* --------------------------------------------------
* DEATH SHOCK HC
import excel using $out/extras.xlsx, sheet(o3.43) clear first
makeRegTable
rename estimate lnemp
gen n=_n
keep parm lnemp n
tempfile p1a
save `p1a'

import excel using $out/extras.xlsx, sheet(o3.44) clear first
makeRegTable
rename estimate lnrev
gen n=_n
keep parm lnrev n
tempfile p1b
save `p1b'

getDHSMainReg 11
gen n=_n
merge 1:1 n using `p1a', nogen
merge 1:1 n using `p1b', nogen
keep parm dhsemp dhsrev lnemp lnrev
order parm dhsemp dhsrev lnemp lnrev
outsheet using $tbl/didhc.tex, delimiter("&") replace noquote non

* --------------------------------------------------
* DEATH SHOCK HP fndr
getDHSMainReg 12
outsheet using $tbl/didhpfndr.tex, delimiter("&") replace noquote non

* --------------------------------------------------
* DEATH SHOCK GT MED AGE fndr
getDHSMainReg 18
outsheet using $tbl/didgtmedfndr.tex, delimiter("&") replace noquote non

* --------------------------------------------------
* CONDITIONAL ON POSITIVE
import excel using $out/extras.xlsx, sheet(o3.28) clear first
makeRegTable
drop if _n==3 | _n==4
procCOPTable "." "." "." "Y" "Y" "Y"
drop if missing(parm)
rename estimate emp
tempfile t1
save `t1'

import excel using $out/extras.xlsx, sheet(o3.29) clear first
makeRegTable
drop if _n==3 | _n==4
procCOPTable "." "." "." "Y" "Y" "Y"
drop if missing(parm)
rename estimate rev
merge 1:1 order using `t1', nogen
save `t1', replace

order parm emp rev
sort order 
drop order
keep parm emp rev
replace parm = "Treated" if parm == "treated"
drop if emp == "." | parm == "surv5"
rename emp Employment
rename rev Revenue
outsheet using $tbl/cop_surv.tex, delimiter("&") replace noquote non

* --------------------------------------------------
* FIXED EFFECTS ROBUST
getDHSMainReg 19
keep parm dhsemp
rename dhsemp dhsempa
gen n=_n
tempfile p1
save `p1'
getDHSMainReg 20
keep parm dhsemp
rename dhsemp dhsempb
gen n=_n
tempfile p2
save `p2'
getDHSMainReg 21
keep parm dhsemp
rename dhsemp dhsempc
gen n=_n
merge 1:1 n using `p1', nogen
merge 1:1 n using `p2', nogen
keep parm dhsempa dhsempb dhsempc
order parm dhsempa dhsempb dhsempc
outsheet using $tbl/didferobust.tex, delimiter("&") replace noquote non

* --------------------------------------------------
* FIXED EFFECTS ROBUST B2B
getDHSMainReg 22
keep parm dhsemp
rename dhsemp dhsempa
gen n=_n
tempfile p1
save `p1'
getDHSMainReg 23
keep parm dhsemp
rename dhsemp dhsempb
gen n=_n
tempfile p2
save `p2'
getDHSMainReg 24
keep parm dhsemp
rename dhsemp dhsempc
gen n=_n
merge 1:1 n using `p1', nogen
merge 1:1 n using `p2', nogen
keep parm dhsempa dhsempb dhsempc
order parm dhsempa dhsempb dhsempc
outsheet using $tbl/didb2bferobust.tex, delimiter("&") replace noquote non

* --------------------------------------------------
* VERY YOUNG FIRM DEATH SHOCKS
getDHSMainReg 25
outsheet using $tbl/didfage25fndr.tex, delimiter("&") replace noquote non

* --------------------------------------------------
* SKILL CORRELATIONS
import excel using $out/extras.xlsx, sheet(o3.30) clear first
replace var = "High Tech" if var=="hitech"
replace var = "Abst Task" if var=="sabst"
replace var = "College" if var=="col"
outsheet using $tbl/corr.tex, delimiter("&") replace noquote non

* --------------------------------------------------
* ALTERNATIVE LHS VARIABLES

import excel using $out/extras.xlsx, sheet(o3.9) clear first
makeRegTable
rename estimate ihsemp
keep parm ihsemp
gen order = _n
tempfile p1a
save `p1a'

import excel using $out/extras.xlsx, sheet(o3.10) clear first
makeRegTable
rename estimate ihsrev
keep parm ihsrev
gen order = _n
tempfile p1b
save `p1b'

import excel using $out/dhs_emp.xlsx, sheet(o1.1) clear first
makeRegTable
rename estimate dhsemp 
keep parm dhsemp
gen order = _n
tempfile p1 
save `p1'
import excel using $out/dhs_emp.xlsx, sheet(o1.2) clear first
makeRegTable
rename estimate dhsemp_ln
keep parm dhsemp_ln
gen order = _n
tempfile p2
save `p2'

import excel using $out/dhs_rev.xlsx, sheet(o2.1) clear first
makeRegTable
rename estimate dhsrev 
keep parm dhsrev
gen order = _n
tempfile p3
save `p3'
import excel using $out/dhs_rev.xlsx, sheet(o2.2) clear first
makeRegTable
rename estimate dhsrev_ln
keep parm dhsrev_ln
gen order = _n
tempfile p4
save `p4'

import excel using $out/extras.xlsx, sheet(o3.11) clear first
makeRegTable
rename estimate revempdth
replace order = _n
tempfile p5
save `p5'
import excel using $out/extras.xlsx, sheet(o3.12) clear first
makeRegTable
rename estimate revempdthscl
replace order = _n
tempfile p6
save `p6'
import excel using $out/extras.xlsx, sheet(o3.15) clear first
makeRegTable
rename estimate revempdth_ln
replace order = _n
tempfile p7
save `p7'
import excel using $out/extras.xlsx, sheet(o3.16) clear first
makeRegTable
rename estimate revempdthscl_ln
replace order = _n
tempfile p8
save `p8'


use `p1a', clear
merge 1:1 order using `p1b', nogen
foreach i of numlist 1(1)8 {
	merge 1:1 order using `p`i'', nogen
}
replace parm = subinstr(parm,"Post","P",.)
replace parm = subinstr(parm,"Treated","T",.)
replace parm = subinstr(parm,"Founder","F",.)
keep parm dhsemp dhsrev ihsemp ihsrev revempdth revempdthscl
order parm dhsemp dhsrev ihsemp ihsrev revempdth revempdthscl 
outsheet using $tbl/alt_lhs_full.tex, delimiter("&") replace noquote non


* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* FIGURES
* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

* --------------------------------------------------
* PRIOR EARNINGS AND EMPLOYMENT GROWTH

import excel using $out/extras.xlsx, sheet(o3.31) clear first
rename prioreex priore
rename estimate estimateej
rename stderr stderrej
tempfile t2
save `t2'
import excel using $out/extras.xlsx, sheet(o3.32) clear first
rename priorekft priore
rename estimate estimatefndr
rename stderr stderrfndr
tempfile t3
save `t3'

use `t2', clear
merge 1:1 priore using `t3', nogen
gen minnintyfiveej = estimateej - 1.96*stderrej
gen maxnintyfiveej = estimateej + 1.96*stderrej
gen p5ej=minnintyfive*maxnintyfive >=0
gen minnintyfivefndr = estimatefndr - 1.96*stderrfndr
gen maxnintyfivefndr = estimatefndr + 1.96*stderrfndr
gen p5fndr=minnintyfivefndr*maxnintyfivefndr >=0
set obs 20
replace priore = 1 if missing(priore)
replace estimateej = 0 if missing(estimateej)
sort priore

#delimit;
tw (rcap minnintyfiveej maxnintyfiveej priore if priore>1, lwidth(medthick) lcolor("gs1"))
  (rcap minnintyfivefndr maxnintyfivefndr priore if priore>1, lwidth(medthick) lcolor("gs9"))
	(scatter estimateej priore if priore==1, mcolor(black) msize(2) msym(plus))
  ,
  graphregion(color(white) lwidth(large)) bgcolor(white) 
  title("")
  ytitle("5-Year Employment Growth", size(medlarge))
  xtitle("Prior Earnings Quantile (5 percentile bins)", size(medlarge))
  ylab(,angle(horizontal) labsize(medlarge))
  xlab(2(2)20, labsize(medlarge))
  yscale(titlegap(*5))
  xscale(titlegap(*5))
  legend(order(1 "Early Joiner" 2 "Founder") region(col(white)) cols(2) size(medlarge)) 
  ;
#delimit cr
graph export  $fig/hcempgr5_panel.png, replace

* --------------------------------------------------
* PRIOR EARNINGS AND PRODUCTIVITY GROWTH
import excel using $out/extras.xlsx, sheet(o3.33) clear first
rename prioreex priore
rename estimate estimateej
rename stderr stderrej
tempfile t2
save `t2'
import excel using $out/extras.xlsx, sheet(o3.34) clear first
rename priorekft priore
rename estimate estimatefndr
rename stderr stderrfndr
tempfile t3
save `t3'

use `t2', clear
merge 1:1 priore using `t3', nogen
gen minnintyfiveej = estimateej - 1.96*stderrej
gen maxnintyfiveej = estimateej + 1.96*stderrej
gen p5ej=minnintyfive*maxnintyfive >=0
gen minnintyfivefndr = estimatefndr - 1.96*stderrfndr
gen maxnintyfivefndr = estimatefndr + 1.96*stderrfndr
gen p5fndr=minnintyfivefndr*maxnintyfivefndr >=0
set obs 20
replace priore = 1 if missing(priore)
replace estimateej = 0 if missing(estimateej)
sort priore

#delimit;
tw (rcap minnintyfiveej maxnintyfiveej priore if priore>1, lwidth(medthick) lcolor("gs1"))
  (rcap minnintyfivefndr maxnintyfivefndr priore if priore>1, lwidth(medthick) lcolor("gs9"))
	(scatter estimateej priore if priore==1, mcolor(black) msize(2) msym(plus))
  ,
  graphregion(color(white) lwidth(large)) bgcolor(white) 
  title("")
  ytitle("5-Year Labor Productivity Growth", size(medlarge))
  xtitle("Prior Earnings Quantile (5 percentile bins)", size(medlarge))
  ylab(,angle(horizontal) labsize(medlarge))
  xlab(2(2)20, labsize(medlarge))
  yscale(titlegap(*1))
  xscale(titlegap(*5))
  legend(order(1 "Early Joiner" 2 "Founder") region(col(white)) cols(2) size(medlarge)) 
  ;
#delimit cr
graph export  $fig/hcprodgr5_panel.png, replace

* --------------------------------------------------
* PRIOR EARNINGS AND EXIT
import excel using $out/extras.xlsx, sheet(o3.35) clear first
rename prioreex priore
rename estimate estimateej
rename stderr stderrej
tempfile t2
save `t2'
import excel using $out/extras.xlsx, sheet(o3.36) clear first
rename priorekft priore
rename estimate estimatefndr
rename stderr stderrfndr
tempfile t3
save `t3'

use `t2', clear
merge 1:1 priore using `t3', nogen
gen minnintyfiveej = estimateej - 1.96*stderrej
gen maxnintyfiveej = estimateej + 1.96*stderrej
gen p5ej=minnintyfive*maxnintyfive >=0
gen minnintyfivefndr = estimatefndr - 1.96*stderrfndr
gen maxnintyfivefndr = estimatefndr + 1.96*stderrfndr
gen p5fndr=minnintyfivefndr*maxnintyfivefndr >=0
set obs 20
replace priore = 1 if missing(priore)
replace estimateej = 0 if missing(estimateej)
sort priore


#delimit;
tw (rcap minnintyfiveej maxnintyfiveej priore if priore>1, lwidth(medthick) lcolor("gs1"))
  (rcap minnintyfivefndr maxnintyfivefndr priore if priore>1, lwidth(medthick) lcolor("gs9"))
	(scatter estimateej priore if priore==1, mcolor(black) msize(2) msym(plus))
  ,
  graphregion(color(white) lwidth(large)) bgcolor(white) 
  title("")
  ytitle("5-Year Firm Death", size(medlarge))
  xtitle("Prior Earnings Quantile (5 percentile bins)", size(medlarge))
  ylab(,angle(horizontal) labsize(medlarge))
  xlab(2(2)20, labsize(medlarge))
  yscale(titlegap(*-30))
  xscale(titlegap(*5))
  legend(order(1 "Early Joiner" 2 "Founder") region(col(white)) cols(2) size(medlarge)) 
  ;
#delimit cr
graph export  $fig/hcexit5_panel.png, replace

* --------------------------------------------------
* ATTRITION COUNTS 
import excel using $out/extras.xlsx, sheet(o3.38) clear first
rename cntex cntej
rename cntkft cntfndr

#delimit;
tw (line cntej yr, lwidth(1) connect(1) lcolor("gs1"))
  (line cntfndr yr, lwidth(1) connect(1) lcolor("gs9") lpat(longdash))
  ,
  graphregion(color(white) lwidth(large)) bgcolor(white) 
  title("")
  ytitle("Mean Active Count", size(medlarge))
  xtitle("Year After Startup", size(medlarge))
  xlab(0(1)5, labsize(medlarge))
  ylabel(0(2)10,angle(horizontal) labsize(medlarge))
  yscale(nofextend)
  xscale(nofextend titlegap(*5))
  legend(order(1 "Early Joiner" 2 "Founder") region(col(white)) cols(2) size(medlarge))
  ;
#delimit cr
graph export $fig/atrtncount.png, replace

* --------------------------------------------------
* ATTRITION AND PRIOR EARNINGS
import excel using $out/extras.xlsx, sheet(o3.37) clear first
rename prioreex prioreej
rename priorekft priorefndr

#delimit;
tw (line prioreej yr, lwidth(1) connect(1) lcolor("gs1") )
  (line priorefndr yr, lwidth(1) connect(1) lcolor("gs9") lpat(longdash) )
  ,
  graphregion(color(white) lwidth(large)) bgcolor(white) 
  title("")
  ytitle("Active FT Mean {it:log(Prior Earnings)}", size(medlarge))
  xtitle("Year After Startup", size(medlarge))
  xlab(0(1)5, labsize(medlarge))
  ylabel(8.4(.2)9.2,angle(horizontal) labsize(medlarge))
  yscale(nofextend titlegap(*5))
  xscale(nofextend titlegap(*5))
  legend(order(1 "Early Joiner" 2 "Founder") region(col(white)) cols(2) size(medlarge))
  ;
#delimit cr
graph export $fig/atrtnhc.png, replace

* --------------------------------------------------
* ASSORTATIVE MATCHING 
import excel using $out/extras.xlsx, sheet(o3.39) clear first
rename prioreexavgbin prioreejavgbin 
rename priorekftbin priorefndrbin
#delimit;
tw  (func y=x, range(1 20) lcolor(gs10) lpat(dash))
  (scatter prioreejavgbin priorefndrbin, mcolor(black) msize(2) msym(circle) mcolor(black))
  ,
  graphregion(color(white) lwidth(large)) bgcolor(white) 
  title("")
  ytitle("Mean Early Joiner Prior Earnings Quantile" , size(medlarge))
  xtitle("Mean Founder Prior Earnings Quantile", size(medlarge))
  ysize(`ysz') xsize(`xsz')
  xlab(2(2)20, labsize(medlarge))
  ylabel(2(2)20,angle(horizontal) labsize(medlarge))
  xscale(range(1 20))
  yscale(range(1 20))
  yscale(nofextend titlegap(*5))
  xscale(nofextend titlegap(*5))
  legend(off)
  ;
#delimit cr
graph export $fig/hcknonkp.png, replace

* --------------------------------------------------
* DEATH SHOCKS EVENT STUDY DHS(EMP)
import excel using $out/dhs_emp.xlsx, sheet(o1.13) clear first
procEJvFDRReg
drop if missing(parm)
set obs 11
replace year_to_death =-1 if missing(year_to_death)
foreach v of varlist estimate minnintyfive maxnintyfive {
	replace `v' = 0 if missing(`v')
}
sort year_to_death
keep year_to_death minnintyfive maxnintyfive estimate p5
foreach v of varlist minnintyfive maxnintyfive estimate p5 {
    rename `v' fdr0_`v'
}
tempfile p1 
save `p1'

import excel using $out/dhs_emp.xlsx, sheet(o1.14) clear first
procEJvFDRReg
drop if missing(parm)
set obs 11
replace year_to_death =-1 if missing(year_to_death)
foreach v of varlist estimate minnintyfive maxnintyfive {
	replace `v' = 0 if missing(`v')
}
sort year_to_death
keep year_to_death minnintyfive maxnintyfive estimate p5
foreach v of varlist minnintyfive maxnintyfive estimate p5 {
    rename `v' fdr1_`v'
}
merge 1:1 year_to_death using `p1'
gen year_to_death_fdr0 = year_to_death-0
gen year_to_death_fdr1 = year_to_death+0
plotFdrVEj "gs1" "gs9" 0.1 -1 "{it:dhs(employment)} Estimate" .1 -1 .2 "dth_dhsemp_fdrVej"

* --------------------------------------------------
* DEATH SHOCKS EVENT STUDY DHS(REV)
import excel using $out/dhs_rev.xlsx, sheet(o2.13) clear first
procEJvFDRReg
drop if missing(parm)
set obs 11
replace year_to_death =-1 if missing(year_to_death)
foreach v of varlist estimate minnintyfive maxnintyfive {
	replace `v' = 0 if missing(`v')
}
sort year_to_death
keep year_to_death minnintyfive maxnintyfive estimate p5
foreach v of varlist minnintyfive maxnintyfive estimate p5 {
    rename `v' fdr0_`v'
}
tempfile p1 
save `p1'

import excel using $out/dhs_rev.xlsx, sheet(o2.14) clear first
procEJvFDRReg
drop if missing(parm)
set obs 11
replace year_to_death =-1 if missing(year_to_death)
foreach v of varlist estimate minnintyfive maxnintyfive {
	replace `v' = 0 if missing(`v')
}
sort year_to_death
keep year_to_death minnintyfive maxnintyfive estimate p5
foreach v of varlist minnintyfive maxnintyfive estimate p5 {
    rename `v' fdr1_`v'
}
merge 1:1 year_to_death using `p1'
gen year_to_death_fdr0 = year_to_death-0
gen year_to_death_fdr1 = year_to_death+0
plotFdrVEj "gs1" "gs9" 0.1 -1 "{it:dhs(revenues)} Estimate" .1 -1 .2 "dth_dhsrev_fdrVej"

* --------------------------------------------------
* DEATH SHOCKS EVENT STUDY LN(EMP)
import excel using $out/extras.xlsx, sheet(o3.1) clear first
procEJvFDRReg
drop if missing(parm)
keep year_to_death minnintyfive maxnintyfive estimate p5
foreach v of varlist minnintyfive maxnintyfive estimate p5 {
    rename `v' fdr0_`v'
}
tempfile p1
save `p1'

import excel using $out/extras.xlsx, sheet(o3.2) clear first
procEJvFDRReg
drop if missing(parm)
keep year_to_death minnintyfive maxnintyfive estimate p5
foreach v of varlist minnintyfive maxnintyfive estimate p5 {
    rename `v' fdr1_`v'
}
merge 1:1 year_to_death using `p1'
gen year_to_death_fdr0 = year_to_death-0
gen year_to_death_fdr1 = year_to_death+0 
keep if year_to_death<=5
plotFdrVEj "gs1" "gs9" .10 -.22 "{it:log(employment)} Estimate" .1 -.2 .05 "dth_lnemp_fdrVej"

* --------------------------------------------------
* DEATH SHOCKS EVENT STUDY LN(REV)
import excel using $out/extras.xlsx, sheet(o3.3) clear first
procEJvFDRReg
drop if missing(parm)
keep year_to_death minnintyfive maxnintyfive estimate p5
foreach v of varlist minnintyfive maxnintyfive estimate p5 {
    rename `v' fdr0_`v'
}
tempfile p1
save `p1'

import excel using $out/extras.xlsx, sheet(o3.4) clear first
procEJvFDRReg
drop if missing(parm)
keep year_to_death minnintyfive maxnintyfive estimate p5
foreach v of varlist minnintyfive maxnintyfive estimate p5 {
    rename `v' fdr1_`v'
}
merge 1:1 year_to_death using `p1'
gen year_to_death_fdr0 = year_to_death-0
gen year_to_death_fdr1 = year_to_death+0
keep if year_to_death<=5
plotFdrVEj "gs1" "gs9" .10 -.22 "{it:log(revenues)} Estimate" .1 -.2 .05 "dth_lnrev_fdrVej"

* --------------------------------------------------
* DEATH SHOCKS EVENT STUDY SECOND YEAR JOINERS DHS(EMP)
import excel using $out/dhs_emp.xlsx, sheet(o1.15) clear first
procEJvFDRReg
drop if missing(parm)
plotSyj "black" .2 -.3 "{it:dhs(employment)} Estimate" .2 -.3 .1 "dth_dhsemp_syj"

* --------------------------------------------------
* DEATH SHOCKS EVENT STUDY SECOND YEAR JOINERS DHS(REV)
import excel using $out/dhs_rev.xlsx, sheet(o2.15) clear first
procEJvFDRReg
drop if missing(parm)
plotSyj "black" .2 -.3 "{it:dhs(revenues)} Estimate" .2 -.3 .1 "dth_dhsrev_syj"

* --------------------------------------------------
* DEATH SHOCKS EVENT STUDY PERSISTENCE DHS(EMP)
import excel using $out/dhs_emp.xlsx, sheet(o1.16) clear first
procEJvFDRReg
keep year_to_death minnintyfive maxnintyfive estimate p5
foreach v of varlist minnintyfive maxnintyfive estimate p5 {
    rename `v' fdr0_`v'
}
tempfile p1 
save `p1'

import excel using $out/dhs_emp.xlsx, sheet(o1.17) clear first
procEJvFDRReg
keep year_to_death minnintyfive maxnintyfive estimate p5
foreach v of varlist minnintyfive maxnintyfive estimate p5 {
    rename `v' fdr1_`v'
}
merge 1:1 year_to_death using `p1'
gen year_to_death_fdr0 = year_to_death-0
gen year_to_death_fdr1 = year_to_death+0
plotFdrVEj "gs1" "gs9" 0.1 -.8 "{it:dhs(employment)} Estimate" .1 -.8 .2 "dth_dhsemp_long"

* --------------------------------------------------
* DEATH SHOCKS EVENT STUDY PERSISTENCE DHS(REV)
import excel using $out/dhs_rev.xlsx, sheet(o2.16) clear first
procEJvFDRReg
keep year_to_death minnintyfive maxnintyfive estimate p5
foreach v of varlist minnintyfive maxnintyfive estimate p5 {
    rename `v' fdr0_`v'
}
tempfile p1 
save `p1'

import excel using $out/dhs_rev.xlsx, sheet(o2.17) clear first
procEJvFDRReg
keep year_to_death minnintyfive maxnintyfive estimate p5
foreach v of varlist minnintyfive maxnintyfive estimate p5 {
    rename `v' fdr1_`v'
}
merge 1:1 year_to_death using `p1'
gen year_to_death_fdr0 = year_to_death-0
gen year_to_death_fdr1 = year_to_death+0
plotFdrVEj "gs1" "gs9" 0.1 -.8 "{it:dhs(revenues)} Estimate" .1 -.8 .2 "dth_dhsrev_long"

* --------------------------------------------------
* FIRM AGE AND EXIT
import excel using $out/extras.xlsx, sheet(o3.40) clear first
egen fa = group(fage)
label define fa 1 "1" 2 "2" 3 "3" 4 "4" 5 "5" 6 "6 to 10" 7 "11 to 20" 8 "21+"
label values fa fa
#delimit;
graph bar fdr fdremp, over(fa, label(angle(45) labsize(medlarge)))
  graphregion(color(white) lwidth(large)) bgcolor(white)
  ytitle("Firm Exit Rate", size(medlarge))
  b1title("Firm Age", size(medlarge))
  bar(1, color(gs1)) bar(2, color(gs10))
  ylabel(, angle(0) labsize(medlarge))
  ysize(`ysz')
  xsize(`xsz')
  legend(order(1 "Firm Weighted" 2 "Employment Weighted") region(col(white)) cols(2) size(medlarge))
  ;
#delimit cr
graph export $fig/firmexit_firmage.png, replace

* --------------------------------------------------
* FIRM AGE AND EMP GROWTH (V1)
import excel using $out/extras.xlsx, sheet(o3.41) clear first
egen fa = group(fage)
label values fa fa
#delimit;
tw (rarea pnintyempgr ptenempgr fa, color(gs14))
  (scatter meanempgr fa, msymbol(circle) msize(2) mcolor(black))
  (scatter medianempgr fa, msymbol(diamond) msize(2) mcolor(gs10))
  , 
  graphregion(color(white) lwidth(large)) bgcolor(white)
  ytitle("Employment Growth Rate", size(medlarge))
  xtitle("Firm Age", size(medlarge))
  ylabel(, angle(0) labsize(medlarge))
  xlabel(1(1)8,valuelabel angle(45) labsize(medlarge))
  ysize(`ysz')
  xsize(`xsz')
  legend(order(1 "90-10" 2 "Mean" 3 "Median") region(col(white)) cols(3) size(medlarge))
  ;
#delimit cr
graph export $fig/empgr_firmage_v1.png, replace

* --------------------------------------------------
* FIRM AGE AND EMP GROWTH (V2)
#delimit;
tw (scatter meanempgr fa, msymbol(circle) msize(2) mcolor(black))
  (scatter medianempgr fa, msymbol(diamond) msize(2) mcolor(gs10))
  , 
  graphregion(color(white) lwidth(large)) bgcolor(white)
  ytitle("Employment Growth Rate", size(medlarge))
  xtitle("Firm Age", size(medlarge))
  ylabel(, angle(0) labsize(medlarge))
  xlabel(1(1)8,valuelabel angle(45) labsize(medlarge))
  ysize(`ysz')
  xsize(`xsz')
  legend(order(1 "Mean" 2 "Median") region(col(white)) cols(2) size(medlarge))
  ;
#delimit cr
graph export $fig/empgr_firmage_v2.png, replace

* --------------------------------------------------
* DEATH SHOCKS EVENT STUDY SECOND YEAR JOINERS DHS(REV)-DHS(EMP)
import excel using $out/extras.xlsx, sheet(o3.7) clear first
procEJvFDRReg
drop if missing(parm)
set obs 11
replace year_to_death =-1 if missing(year_to_death)
foreach v of varlist estimate minnintyfive maxnintyfive {
	replace `v' = 0 if missing(`v')
}
sort year_to_death
keep year_to_death minnintyfive maxnintyfive estimate p5
foreach v of varlist minnintyfive maxnintyfive estimate p5 {
    rename `v' fdr0_`v'
}
tempfile p1
save `p1'
import excel using $out/extras.xlsx, sheet(o3.8) clear first
procEJvFDRReg
drop if missing(parm)
set obs 11
replace year_to_death =-1 if missing(year_to_death)
foreach v of varlist estimate minnintyfive maxnintyfive {
	replace `v' = 0 if missing(`v')
}
sort year_to_death
keep year_to_death minnintyfive maxnintyfive estimate p5
foreach v of varlist minnintyfive maxnintyfive estimate p5 {
    rename `v' fdr1_`v'
}
merge 1:1 year_to_death using `p1'
gen year_to_death_fdr0 = year_to_death-0
gen year_to_death_fdr1 = year_to_death+0
plotFdrVEj "gs1" "gs9" 0.1 -.2 "{it:dhs(revenues)}-{it:dhs(employment)} Estimate" .1 -.2 .05 "dth_dhsrevemp_fdrVej"

* --------------------------------------------------
* DEATH SHOCK COX SURVIVAL
import excel using $out/extras.xlsx, sheet(o3.42) clear first
set obs 6
rename surv1 survone
rename surv0 survzero
replace survzero=1 if missing(yr)
replace survone=1 if missing(yr)
replace yr = 0 if missing(yr)
gen minnintyfivezero = survzero - 1.96*stderr
gen maxnintyfivezero = survzero + 1.96*stderr
gen minnintyfiveone = survone - 1.96*stderr
gen maxnintyfiveone = survone + 1.96*stderr
sort yr

#delimit;
tw (line survone yr, lcolor(black) lwidth(1))
  (line survzero yr, lcolor(gs10) lpattern(shortdash) lwidth(1))
  ,
  graphregion(color(white) lwidth(large)) bgcolor(white)
  title("")
  ytitle("Survival Estimate", size(medlarge))
  xtitle("Year Relative to Death", size(medlarge))
  ysize(`ysz') xsize(`xsz')
  xlab(0(1)5, labsize(medlarge))
  ylabel(.5(.1)1,angle(horizontal) labsize(medlarge))
  xscale(range(0 5))
  yscale(range(.45 1))
  yscale(nofextend)
  xscale(nofextend)
  legend(off)
  text(.73 4 "Control", place(e))
  text(.58 3 "Treated", place(e))
  ;
#delimit cr
graph export $fig/dth_surv.png, replace

* --------------------------------------------------
* DEATH SHOCKS EVENT STUDY SECOND YEAR JOINERS LN(EMP)
import excel using $out/extras.xlsx, sheet(o3.5) clear first
procEJvFDRReg
drop if missing(parm)
plotSyj "black" .2 -.3 "{it:log(employment)} Estimate" .2 -.3 .1 "dth_lnemp_syj"

* --------------------------------------------------
* DEATH SHOCKS EVENT STUDY SECOND YEAR JOINERS LN(REV)
import excel using $out/extras.xlsx, sheet(o3.6) clear first
procEJvFDRReg
plotSyj "black" .2 -.3 "{it:log(revenues)} Estimate" .2 -.3 .1 "dth_lnrev_syj"

* --------------------------------------------------
* EDIT END OF LINES FOR TEX FILES 
* --------------------------------------------------

local files : dir "$tbl/" files "*.tex"
foreach f in `files' {
	* regression math in labels
    shell sed -i '' -e 's/\\times/ \$\\times\$ /g' $tbl/`f'

    * end of line tex table 
    shell sed -i '' -e 's/$/ \\\\ /' $tbl/`f'
}

di c(current_date) " " c(current_time)
log close


